import xlwings as xw
import pandas as pd
# wb = xw.Book()
# wb.save("D:/ITaudit/pandas_excel/lang_ge.xlsx")
# wb.close()

df = pd.read_excel('D:\ITAudit\pandas_excel\pc\Stu_new_1score_list.xlsx',index_col='学号')
df['总分'] = df['语文'] + df['数学'] + df['英语']  # 三列求和相加
# df['总分'] = df[['省份', '语文', '数学', '英语']].sum(axis=1)#此处有提示
df1 = df[['省份', '语文', '数学', '英语', '总分']]
average_scores = df1.groupby('省份').agg(['mean'])  # 以省份分组三科平均分
average_scores_df = average_scores.round(2)
with pd.ExcelWriter('D:\ITAudit\pandas_excel\pc\Stu_new_1score_list.xlsx',mode='a') as writer:#追加一个sheet
    average_scores_df.to_excel(writer,sheet_name='各省份平均分')